/*
 * basic_transaction application showing the transaction support usage of the dbConnect API
 * Copyright (C) 2004 Johnathan Ingram, jingram@rogueware.org
 *
 * This library is free software; you can redistribute it and/or
 *   modify it under the terms of the GNU Lesser General Public
 *   License as published by the Free Software Foundation; either
 *   version 2.1 of the License, or (at your option) any later version.
 *
 *   This library is distributed in the hope that it will be useful,
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
 *   Lesser General Public License for more details.
 *
 *   You should have received a copy of the GNU Lesser General Public
 *   License along with this library; if not, write to the Free Software
 *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  US
 *
 * Note: This example requires the TransactionTest table as defined in the SQL for each database type
 *       The example uses two database connections, showing the state of data on each as transactions are performed
 */


#include <iostream>
#include <map>
#include <string>


#include "dbconn/dbconnect.h"

using namespace std;

void 
showTableData(
      DbQueryVar &conn1,
      DbConnectionVar &driver)
   throw(
      BaseException)
{
   // Create a second connection
   DbQueryVar conn2 = driver->requestQueryConnection();
   
   // Execute the same query on both connections
   string sqlQuery =
      "SELECT "
      "  id, description "
      "FROM "
      "  TransactionTest ";

  
   // Connection 1: List the contents of the table
   conn1->command(sqlQuery);
   conn1->execute();
   
   cout << "Connection 1: TransactionTest table data: " << endl;
   cout << "----------------------------------------- " << endl;
   cout << "id\tdescription" << endl;
   cout << "--\t-----------" << endl;
   if (conn1->eof())
      cout << "No rows in result set for this query" << endl;
   else
   {
      while (!conn1->eof())
      {
         conn1->fetchNext();
         cout << conn1->getFieldByName("id")->asString() << "\t" << conn1->getFieldByName("description")->asString() << endl;
      }
   }  
   cout << endl;   
   
   // Connection 2: List the contents of the table
   conn2->command(sqlQuery);
   conn2->execute();
   
   cout << "Connection 2: TransactionTest table data: " << endl;
   cout << "----------------------------------------- " << endl;
   cout << "id\tdescription" << endl;
   cout << "--\t-----------" << endl;
   if (conn2->eof())
      cout << "No rows in result set for this query" << endl;
   else
   {
      while (!conn2->eof())
      {
         conn2->fetchNext();
         cout << conn2->getFieldByName("id")->asString() << "\t" << conn2->getFieldByName("description")->asString() << endl;
      }
   }  
   cout << endl;      
}



int 
main(
      int argc, char** argv)
{
   map<string, DbConnection::Driver> drivers;

   drivers["MYSQL"] =       DbConnection::MYSQL;
   drivers["MSQL"] =        DbConnection::MSQL;
   drivers["POSTGRESQL"] =  DbConnection::POSTGRESQL;
   drivers["DB2"] =         DbConnection::DB2;

   if (argc == 2)
   {
      // Use smart pointers. Must be declared outside of the try
      //   as if an exception is caught it will loose scope and free. 
      DbConnectionVar driver;
      DbQueryVar      conn1;

      string sqlQuery = 
         "INSERT INTO TransactionTest " 
         "  (id, description) "
         "VALUES "
         "  (:id, :description) ";
      
      try
      {
         driver = new DbConnection(drivers[argv[1]]);

         // Connect to the database. (DB2 takes different conn string)
         if (drivers[argv[1]] == DbConnection::DB2)
         {
            cout << "WARNING: DB2's locking will cause this program to hang" << endl;
            driver->connect("dbconn", "letmein", "dbConnDB", "", 5, 2);
         }
         else
         {
            driver->connect("dbconnect", "letmein", "dbConnectDB", "localhost", 5, 2);
         }

         // Get a query connection object
         conn1 = driver->requestQueryConnection();
         
         
         
         // Test 1: Insert data with commit
         cout << "TEST 1: Inserting data and commiting" << endl;
         cout << "------------------------------------" << endl << endl;
         
         // Note: You dont have to call transBegin as the first query will always 
         //       implicitly create a transaction if one does not exist.
         conn1->transBegin();

         cout << "Data on connections before record insert.." << endl;                  
         showTableData(conn1, driver);
         cout << endl;

         // Insert the data using connection 1         
         conn1->command(sqlQuery);
         conn1->bindParam("id")->setLong(1);
         conn1->bindParam("description")->setString("RECORD ONE");
         conn1->execute();
            
         cout << "Data on connections after record insert.." << endl;         
         showTableData(conn1, driver);
         cout << endl;
            
         // Commit on connection one to make the data final
         conn1->commit();
         
         cout << "Data on connections after commit.." << endl;         
         showTableData(conn1, driver);
         cout << endl;




         // Test 2: Insert data with rollback
         cout << endl << endl;
         cout << "TEST 2: Inserting data and rolling back" << endl;
         cout << "------------------------------------" << endl << endl;
         

         cout << "Data on connections before record insert.." << endl;                  
         showTableData(conn1, driver);
         cout << endl;
         
         // Insert the data using connection 1         
         conn1->command(sqlQuery);
         conn1->bindParam("id")->setLong(2);
         conn1->bindParam("description")->setString("RECORD TWO");
         conn1->execute();

         cout << "Data on connections after record insert.." << endl;         
         showTableData(conn1, driver);
         cout << endl;
            
         // Rollback on connection one to get rid of data
         conn1->rollback();
         
         cout << "Data on connections after rollback.." << endl;         
         showTableData(conn1, driver);
         cout << endl;



         // Finnally clean all the data out the table
         cout << "TEST 3: Deleting all data and commiting" << endl;
         cout << "------------------------------------" << endl << endl;
         sqlQuery = 
            "DELETE FROM TransactionTest WHERE id IN (1, 2)" ;
         
         conn1->command(sqlQuery);
         conn1->execute();
         conn1->commit();                  

         cout << "Data on connections after delete and commit" << endl;         
         showTableData(conn1, driver);
         cout << endl;


         // Note: If you do not call commit or rollback on a connection,
         //       the connection will rollback automatically. 
         //       This is implemented so if an exception is thrown, a rollback will automatically occur.
      }
      catch(BaseException &ex)
      {
         // Only need to catch a single exception. Can use 'name' etc to determine the actual exception
         cout << "DbConnect Exception: " << ex.name << endl 
              << "  " << ex.code << " : " << ex.description << endl;
      }
      catch(...)
      {
         cout << "An Unknown exception has been trapped!\n" << endl;
      }
      cout << endl;
        
      // The 'conn' and 'driver' objects are smart pointers and will cleanup as soon as they go out of scope      
   }
   else
   {
      cout << "Syntax: basic_transaction DRIVER" << endl;
      cout << "Drivers: MYSQL" << endl;
      cout << "       : MSQL" << endl;
      cout << "       : POSTGRESQL" << endl;
      cout << "       : DB2" << endl;
      return 1;
   }

   return 0;
}
